﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;
using MySql.Data.MySqlClient;
using MySql.Data.Types;
using System.Data;
using Thrift.Route;
using log4net;
namespace WebServiceSmart
{
    public class mysqlcli
    {
        public MySqlConnection DBConn = null;
        public static string sqlpwd = "juhesheng01";
        public mysqlcli()
        {

        }
        ~mysqlcli()
        {
            this.closesql();
        }
        //打开路由
        public bool openRoutingServicesql()
        {
            try
            {
                string loginfo = string.Format("Data Source=qingguan.mysql.rds.aliyuncs.com;Database={0};User ID=qingguan;Password={1};port=3377;charset=utf8;",
                   "qgwuliusystem", "QG568895qg");
                DBConn = new MySqlConnection(loginfo);
                DBConn.Open();
            }
            catch
            {
                DBConn.Close();
                DBConn.Dispose();
                DBConn = null;
                return false;
            }
            return true;
        }
        //打开用户数据库
        public bool openYongHuServicesql()
        {
            try
            {
                //string loginfo = string.Format("Data Source=127.0.0.1;Database={0};User ID=root;Password=123456;port=3306;charset=utf8;",
                //        "shopping");
                //正式
                //string loginfo = string.Format("Data Source=rm-bp1pt1s7o8jwp6s2u90130.mysql.rds.aliyuncs.com;Database={0};User ID=juhesheng;Password={1};port=3306;charset=utf8;",
                //   "yonghulaodinpo", sqlpwd);
                //测试
                string loginfo = string.Format("Data Source=rm-bp1pt1s7o8jwp6s2u90130.mysql.rds.aliyuncs.com;Database={0};User ID=juhesheng;Password={1};port=3306;charset=utf8;",
                    "yonghulaodingpotest", sqlpwd);

#if DEBUG
                loginfo = string.Format("Data Source=127.0.0.1;Database={0};User ID=root;Password=123456;port=3306;charset=utf8;",
                "yonghulaodinpo");
#endif

                DBConn = new MySqlConnection(loginfo);
                DBConn.Open();
            }
            catch
            {
                DBConn.Close();
                DBConn.Dispose();
                DBConn = null;
                return false;
            }
            return true;
        }
        //打开运力数据库
        public bool openYunLiServicesql()
        {
            try
            {
                string loginfo = string.Format("Data Source=qingguan.mysql.rds.aliyuncs.com;Database={0};User ID=qingguan;Password={1};port=3377;charset=utf8;",
                   "yonghulaodinpo", "QG568895qg");

                DBConn = new MySqlConnection(loginfo);
                DBConn.Open();
            }
            catch
            {
                DBConn.Close();
                DBConn.Dispose();
                DBConn = null;
                return false;
            }
            return true;
        }
        //打开商城数据库
        public bool openShopServicesql()
        {
            try
            {
                //string loginfo = string.Format("Data Source=127.0.0.1;Database={0};User ID=root;Password=123456;port=3306;charset=utf8;",
                //"yonghusystem");
                //正式
                //string loginfo = string.Format("Data Source=rm-bp1pt1s7o8jwp6s2u90130.mysql.rds.aliyuncs.com;Database={0};User ID=juhesheng;Password={1};port=3306;charset=utf8;",
                //   "yonghulaodinpo", sqlpwd);
                //测试
                string loginfo = string.Format("data source=rm-bp1pt1s7o8jwp6s2u90130.mysql.rds.aliyuncs.com;database={0};user id=juhesheng;password={1};port=3306;charset=utf8;",
                    "yonghulaodingpotest", sqlpwd);

#if DEBUG
                loginfo = string.Format("Data Source=127.0.0.1;Database={0};User ID=root;Password=123456;port=3306;charset=utf8;",
                "yonghulaodinpo");
#endif
                DBConn = new MySqlConnection(loginfo);
                DBConn.Open();
            }
            catch
            {
                DBConn.Close();
                DBConn.Dispose();
                DBConn = null;
                return false;
            }
            return true;
        }
        public bool opensql(SqlInfo sqlinfo)
        {
            try
            {
                string loginfo = string.Format("Data Source={0};Database={1};User ID={2};Password={3};port={4};charset=utf8;",
                    sqlinfo.String_sqlip, sqlinfo.String_sqlname, sqlinfo.String_sqluser, sqlinfo.String_sqlpwd, sqlinfo.String_sqlport);
                DBConn = new MySqlConnection(loginfo);
                DBConn.Open();
            }
            catch
            {
                DBConn.Close();
                DBConn.Dispose();
                DBConn = null;
                return false;
            }
            return true;
        }

        public int SqlExcuteNoReturn(string command, MySqlTransaction tr = null)
        {
            int ds = -1;
            try
            {
                MySqlCommand cmd = new MySqlCommand(command, DBConn);
                if (tr != null)
                {
                    cmd.Connection = tr.Connection;
                    cmd.Transaction = tr;
                    cmd.CommandText = command;
                }
                else
                {
                    cmd.Connection = DBConn;
                    cmd.CommandText = command;
                }

                ds = cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                ILog logJieKou = LogManager.GetLogger("LoggerJieKou");
                logJieKou.Info(command + e.Message);
                throw e;
            }
            return ds;
        }

        public DataTable SqlExcuteWithReturn(string command, MySqlParameter[] paras = null)
        {

            MySqlDataAdapter adp = new MySqlDataAdapter();
            DataTable ds = new DataTable();
            try
            {
                var cmd = new MySqlCommand(command, DBConn);
                if (paras != null)
                    cmd.Parameters.AddRange(paras);
                adp.SelectCommand = cmd;

                adp.Fill(ds);
            }
            catch (Exception e)
            {
                ILog logJieKou = LogManager.GetLogger("LoggerJieKou");
                logJieKou.Info(command + e.Message);
                return ds;
            }
            return ds;
        }
        public int SqlExcuteNoReturn(string command)
        {
            int ds = -1;
            try
            {
                MySqlCommand cmd = new MySqlCommand(command, DBConn);
                ds = cmd.ExecuteNonQuery();

            }
            catch (Exception e)
            {
                ILog logJieKou = LogManager.GetLogger("LoggerJieKou");
                logJieKou.Info(command + e.Message);
            }
            return ds;
        }
        public int SqlExcuteNoReturn(string command, params MySqlParameter[] paras)
        {
            int ds = -1;
            try
            {
                MySqlCommand cmd = new MySqlCommand(command, DBConn);
                cmd.Parameters.AddRange(paras);

                ds = cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                ILog logJieKou = LogManager.GetLogger("LoggerJieKou");
                logJieKou.Info(e.Message);
                logJieKou.Info(command);
                logJieKou.Info(paras);
            }
            return ds;
        }
        public object SqlExecuteScalar(string command, MySqlParameter[] paras = null)
        {
            try
            {
                MySqlCommand cmd = new MySqlCommand(command, DBConn);
                if (paras != null)
                    cmd.Parameters.AddRange(paras);

                return cmd.ExecuteScalar();
            }
            catch (Exception e)
            {
                ILog logJieKou = LogManager.GetLogger("LoggerJieKou");
                logJieKou.Info(e.Message);
                logJieKou.Info(command);
                if (paras != null) logJieKou.Info(paras);
            }
            return null;
        }
        public string SqlExcuteCountReturn(string command)
        {
            MySqlDataAdapter adp = new MySqlDataAdapter();
            DataTable ds = new DataTable();
            try
            {
                adp.SelectCommand = new MySqlCommand(command, DBConn);
                adp.Fill(ds);
                double allcount = 0;
                for (int i = 0; i < ds.Rows.Count; i++)
                {
                    double nowcount = 0;
                    double.TryParse(ds.Rows[i][0].ToString(), out nowcount);
                    allcount += nowcount;
                }
                ds.Dispose();
                return allcount.ToString();
            }
            catch (Exception e)
            {
                ILog logJieKou = LogManager.GetLogger("LoggerJieKou");
                logJieKou.Info(command + e.Message);
                ds.Dispose();
                return "0";
            }
        }
        public void closesql()
        {
            if (DBConn != null)
            {
                DBConn.Close();
                DBConn.Dispose();
            }
            DBConn = null;
        }
        public MySqlConnection Connection()
        {
            return DBConn;
        }
    }
}
